set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_route_whole_direct_base_dt
select
    in_from_code
     ,update_date
     ,in_from_provider_code
     ,in_from_city_code
     ,in_from_area_code
     ,out_to_provider_code
     ,out_to_city_code
     ,out_to_area_code
     ,out_to_code
     ,in_branch_id
     ,main_id
     ,out_branch_id
     ,in_collect_code
     ,in_to_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 or direct_route_type = 2 then in_to_code else start_center_code end start_center_code
     ,search_type
     ,start_network_code
     ,in_from_regional_code
     ,in_from_regional_desc
     ,in_from_financial_center_code
     ,in_from_financial_center_desc
     ,in_from_provider_desc
     ,in_from_city_desc
     ,in_from_area_desc
     ,in_collect_name
     ,in_from_name
     ,in_to_name
     ,in_edge_latest_warehousing
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e1_planned_departure else in_edge_planned_departure  end as in_edge_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e1_planned_arrival   else in_edge_planned_arrival    end as in_edge_planned_arrival
     ,case when route_contain_main_line = 1 and direct_route_type = 2 and in_collect_line_name is null then e1_planned_departure else in_edge2_planned_departure end as in_edge2_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 2 and in_collect_line_name is null then e1_planned_arrival   else in_edge2_planned_arrival   end as in_edge2_planned_arrival
     ,branch_in_span_days
     ,branch_in_total_time
     ,is_main_route
     ,transfer_type
     ,num_of_transfer as num_of_transfer --默认
     ,whole_route
     ,e1_center_flow
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e2_line_code
           when route_contain_main_line = 1 and direct_route_type = 2 then e2_line_code
           when route_contain_main_line = 1 and direct_route_type = 3 and e2_line_code is null then out_collect_line_name
           when route_contain_main_line = 1 and direct_route_type = 4 and e2_line_code is null then out_network_line_name
           else e1_line_code end as e1_line_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e2_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 2 then e2_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 3 and e2_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e2_line_code is null then null
           else e1_planned_departure end as e1_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e2_runtime
           when route_contain_main_line = 1 and direct_route_type = 2 then e2_runtime
           when route_contain_main_line = 1 and direct_route_type = 3 and e2_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e2_line_code is null then null
           else  e1_runtime end as e1_runtime
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e2_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 2 then e2_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 3 and e2_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e2_line_code is null then null
           else e1_planned_arrival end as e1_planned_arrival
     --未修改
     ,e1_e2_stop_time
     --修改点
     ,e2_center_flow
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e3_line_code
           when route_contain_main_line = 1 and direct_route_type = 2 then e3_line_code
           when route_contain_main_line = 1 and direct_route_type = 3 and e3_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e3_line_code is null then null
           else e2_line_code end as e2_line_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e3_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 2 then e3_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 3 and e3_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e3_line_code is null then null
           else e2_planned_departure end as e2_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e3_runtime
           when route_contain_main_line = 1 and direct_route_type = 2 then e3_runtime
           when route_contain_main_line = 1 and direct_route_type = 3 and e3_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e3_line_code is null then null
           else e2_runtime end as e2_runtime
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e3_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 2 then e3_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 3 and e3_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e3_line_code is null then null
           else e2_planned_arrival end as e2_planned_arrival
     ,e2_e3_stop_time
     ,e3_center_flow
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e4_line_code
           when route_contain_main_line = 1 and direct_route_type = 2 then e4_line_code
           when route_contain_main_line = 1 and direct_route_type = 3 and e5_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e5_line_code is null then null
           else e3_line_code end as e3_line_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e4_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 2 then e4_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 3 and e4_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e4_line_code is null then null
           else e3_planned_departure end as e3_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e4_runtime
           when route_contain_main_line = 1 and direct_route_type = 2 then e4_runtime
           when route_contain_main_line = 1 and direct_route_type = 3 and e4_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e4_line_code is null then null
           else e3_runtime end as e3_runtime
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e4_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 2 then e4_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 3 and e4_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e4_line_code is null then null
           else e3_planned_arrival end as e3_planned_arrival
     ,e3_e4_stop_time
     ,e4_center_flow
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e5_line_code
           when route_contain_main_line = 1 and direct_route_type = 2 then e5_line_code
           when route_contain_main_line = 1 and direct_route_type = 3 and e5_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e5_line_code is null then null
           else e4_line_code end as e4_line_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e5_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 2 then e5_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 3 and e5_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e5_line_code is null then null
           else e4_planned_departure end as e4_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e5_runtime
           when route_contain_main_line = 1 and direct_route_type = 2 then e5_runtime
           when route_contain_main_line = 1 and direct_route_type = 3 and e5_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e5_line_code is null then null
           else e4_runtime end as e4_runtime
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e5_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 2 then e5_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 3 and e5_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e5_line_code is null then null
           else e4_planned_arrival end as e4_planned_arrival
     ,e4_e5_stop_time
     ,e5_center_flow
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e6_line_code
           when route_contain_main_line = 1 and direct_route_type = 2 then e6_line_code
           when route_contain_main_line = 1 and direct_route_type = 3 and e6_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e6_line_code is null then null
           else e5_line_code end as e5_line_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e6_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 2 then e6_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 3 and e6_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e6_line_code is null then null
           else e5_planned_departure end as e5_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e6_runtime
           when route_contain_main_line = 1 and direct_route_type = 2 then e6_runtime
           when route_contain_main_line = 1 and direct_route_type = 3 and e6_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e6_line_code is null then null
           else e5_runtime end as e5_runtime
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e6_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 2 then e6_planned_arrival
           when route_contain_main_line = 1 and direct_route_type = 3 and e6_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e6_line_code is null then null
           else e5_planned_arrival end as e5_planned_arrival
     ,e5_e6_stop_time
     ,e6_center_flow
     ,case when route_contain_main_line = 1 and direct_route_type = 1 and out_collect_line_name is not null then null --out_collect_line_name
           when route_contain_main_line = 1 and direct_route_type = 2 and out_collect_line_name is not null then null --out_collect_line_name
           when route_contain_main_line = 1 and direct_route_type = 3 and out_collect_line_name is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and out_collect_line_name is null then null
           else e6_line_code end as e6_line_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 and out_collect_line_name is not null then null --out_edge2_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 2 and out_collect_line_name is not null then null --out_edge2_planned_departure
           when route_contain_main_line = 1 and direct_route_type = 3 and out_collect_line_name is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and out_collect_line_name is null then null
           else e6_planned_departure end as e6_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 1 and out_collect_line_name is not null then null --out_edge_run_time
           when route_contain_main_line = 1 and direct_route_type = 2 and out_collect_line_name is not null then null --out_edge_run_time
           when route_contain_main_line = 1 and direct_route_type = 3 and out_collect_line_name is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and out_collect_line_name is null then null
           else e6_runtime end as e6_runtime
     ,case when route_contain_main_line = 1 and direct_route_type = 1 and out_collect_line_name is not null then null --out_edge_planned_arrival_time
           when route_contain_main_line = 1 and direct_route_type = 2 and out_collect_line_name is not null then null --out_edge_planned_arrival_time
           when route_contain_main_line = 1 and direct_route_type = 3 and out_collect_line_name is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and out_collect_line_name is null then null
           else e6_planned_arrival end as e6_planned_arrival
     ,e6_e7_stop_time
     ,e1_end_code
     ,e2_end_code
     ,e3_end_code
     ,e4_end_code
     ,e5_end_code
     ,e6_end_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then cast(nvl(transfer_total_span_days, 0) + in_edge_span_days as int)
           when route_contain_main_line = 1 and direct_route_type = 2 then cast(nvl(transfer_total_span_days, 0) + in_edge2_span_days as int)
           when route_contain_main_line = 1 and direct_route_type = 3 then cast(nvl(transfer_total_span_days, 0) + out_edge_span_days as int)
           when route_contain_main_line = 1 and direct_route_type = 4 then cast(nvl(transfer_total_span_days, 0) + out_edge2_span_days as int)
           else transfer_total_span_days end as transfer_total_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then cast(nvl(transfer_total_time_use, 0) + nvl(branch_in_center_stop_time, 0) as int)
           when route_contain_main_line = 1 and direct_route_type = 2 then cast(nvl(transfer_total_time_use, 0) + nvl(branch_in_center_stop_time, 0) as int)
           when route_contain_main_line = 1 and direct_route_type = 3 then cast(nvl(transfer_total_time_use, 0) + nvl(center_branch_out_stop_time, 0) as int)
           when route_contain_main_line = 1 and direct_route_type = 4 then cast(nvl(transfer_total_time_use, 0) + nvl(center_branch_out_stop_time, 0) as int)
           else transfer_total_time_use end as transfer_total_time_use
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then cast(nvl(main_total_span_days, 0) + nvl(branch_in_center_span_days, 0) as int)
           when route_contain_main_line = 1 and direct_route_type = 2 then cast(nvl(main_total_span_days, 0) + nvl(branch_in_center_span_days, 0) as int)
           when route_contain_main_line = 1 and direct_route_type = 3 then cast(nvl(main_total_span_days, 0) + nvl(center_branch_out_span_days, 0) as int)
           when route_contain_main_line = 1 and direct_route_type = 4 then cast(nvl(main_total_span_days, 0) + nvl(center_branch_out_span_days, 0) as int)
           else main_total_span_days end as main_total_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then cast(nvl(main_total_time_use, 0) + in_edge_run_time as int)
           when route_contain_main_line = 1 and direct_route_type = 2 then cast(nvl(main_total_time_use, 0) + in_edge2_run_time as int)
           when route_contain_main_line = 1 and direct_route_type = 3 then cast(nvl(main_total_time_use, 0) + out_edge_run_time as int)
           when route_contain_main_line = 1 and direct_route_type = 4 then cast(nvl(main_total_time_use, 0) + out_edge2_run_time as int)
           else main_total_time_use end as main_total_time_use
     ,case when route_contain_main_line = 1 and direct_route_type = 3 or direct_route_type = 4 then null
           else end_center end end_center
     ,case when route_contain_main_line = 1 and direct_route_type = 3 or direct_route_type = 4 then null
           else end_center_code end end_center_code
     ,case when route_contain_main_line = 1 and direct_route_type = 1 or direct_route_type = 2 then null
           else start_center end start_center
     ,case when route_contain_main_line = 1 and direct_route_type = 3 or direct_route_type = 4 then null
           else out_from_code end out_from_code
     ,out_collect_code
     ,out_to_regional_desc
     ,out_to_regional_code
     ,out_to_financial_center_desc
     ,out_to_financial_center_code
     ,out_to_provider_desc
     ,out_to_city_desc
     ,out_to_area_desc
     ,out_collect_name
     ,out_to_name
     --用不到
     ,case when route_contain_main_line = 1 and direct_route_type = 3 or direct_route_type = 4 then null else out_from_name end out_from_name
     ,out_has_collect
     ,case when route_contain_main_line = 1 and direct_route_type = 3 then coalesce(e6_planned_departure,e5_planned_departure,e4_planned_departure,e3_planned_departure,e2_planned_departure,e1_planned_departure) else out_edge_planned_departure end as out_edge_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 3 then coalesce(e6_planned_arrival,e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival) else out_edge_planned_arrival_time end as out_edge_planned_arrival_time
     ,case when route_contain_main_line = 1 and direct_route_type = 4 then coalesce(e6_planned_departure,e5_planned_departure,e4_planned_departure,e3_planned_departure,e2_planned_departure,e1_planned_departure) else out_edge2_planned_departure end as out_edge2_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 4 then coalesce(e6_planned_arrival,e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival) else out_edge2_planned_arrival_time end as out_edge2_planned_arrival_time
     ,out_edge2_latest_warehouse_time --默认
     ,out_edge_latest_warehouse_time --默认
     ,out_edge_span_days_sign
     ,out_edge2_span_days_sign
     ,out_edge_deadline_sign_time
     ,out_edge2_deadline_sign_time
     ,branch_out_span_days -- 20210924由branch_out_span_days改为branch_out_deliver_span_days
     ,branch_out_total_time
     ,effective_date
     ,expiration_date
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then get_span_time(in_edge_latest_warehousing, e1_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 2 then get_span_time(e1_planned_arrival, e2_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 3 and e1_line_code is null and in_edge2_planned_arrival is not null then get_span_time(in_edge2_planned_arrival, out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 3 and e1_line_code is null and in_edge2_planned_arrival is null then get_span_time(in_edge_planned_arrival, out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 4 and e1_line_code is null and in_edge2_planned_arrival is not null then get_span_time(in_edge2_planned_arrival, out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 4 and e1_line_code is null and in_edge2_planned_arrival is null then get_span_time(in_edge_planned_arrival, out_edge_planned_departure)
           else branch_in_center_stop_time end as branch_in_center_stop_time
     ,case when route_contain_main_line = 1 and direct_route_type = 1 and e2_line_code is null and out_edge_planned_departure is not null then get_span_time(e1_planned_arrival, out_edge_latest_warehouse_time) + get_span_time(out_edge_latest_warehouse_time, out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 1 and e2_line_code is null and out_edge_planned_departure is null then get_span_time(e1_planned_arrival, out_edge2_latest_warehouse_time) + get_span_time(out_edge2_latest_warehouse_time, out_edge2_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 2 and e2_line_code is null and out_edge_planned_departure is not null then get_span_time(e1_planned_arrival, out_edge_latest_warehouse_time) + get_span_time(out_edge_latest_warehouse_time, out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 2 and e2_line_code is null and out_edge_planned_departure is null then get_span_time(e1_planned_arrival, out_edge2_latest_warehouse_time) + get_span_time(out_edge2_latest_warehouse_time, out_edge2_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 3 then get_span_time(out_edge_planned_arrival_time, out_edge2_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 4 then null
           else  center_branch_out_stop_time end as center_branch_out_stop_time
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then get_span_days(in_edge_latest_warehousing, e1_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 2 then get_span_days(e1_planned_arrival, e2_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 3 and e2_line_code is null and in_edge2_planned_arrival is not null then get_span_days(in_edge2_planned_arrival, e1_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 3 and e2_line_code is null and in_edge2_planned_arrival is null then get_span_days(in_edge_planned_arrival, e1_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 4 and e2_line_code is null and in_edge2_planned_arrival is not null then get_span_days(in_edge2_planned_arrival, e1_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 4 and e2_line_code is null and in_edge2_planned_arrival is null then get_span_days(in_edge_planned_arrival, e1_planned_departure)
           else branch_in_center_span_days end as branch_in_center_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 and e2_line_code is null and out_edge_planned_departure is not null then get_span_days(e1_planned_arrival, out_edge_latest_warehouse_time) + get_span_days(out_edge_latest_warehouse_time, out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 1 and e2_line_code is null and out_edge_planned_departure is null then get_span_days(e1_planned_arrival, out_edge2_latest_warehouse_time) + get_span_days(out_edge2_latest_warehouse_time, out_edge2_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 2 and e2_line_code is null and out_edge_planned_departure is not null then get_span_days(e1_planned_arrival, out_edge_latest_warehouse_time) + get_span_days(out_edge_latest_warehouse_time, out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 2 and e2_line_code is null and out_edge_planned_departure is null then get_span_days(e1_planned_arrival, out_edge2_latest_warehouse_time) + get_span_days(out_edge2_latest_warehouse_time, out_edge2_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 3 then get_span_days(coalesce(e6_planned_arrival,e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival), out_edge_planned_departure)
           when route_contain_main_line = 1 and direct_route_type = 4 then 0
           else center_branch_out_span_days end as center_branch_out_span_days
     ,is_circuitous
     ,out_edge_span_days_arrive
     ,out_edge2_span_days_arrive
     ,in_nodes
     ,out_nodes
     ,total_days_use
     ,total_time_use
     ,total_days_t
     ,total_nodes
     ,working_days
     ,extra_in_collect_code
     ,extra_in_collect_name
     ,case when route_contain_main_line = 1 and direct_route_type = 2 and length(in_collect_line_name)>0 then e1_line_code else extra_in_line_name end extra_in_line_name
     ,extra_in_latest_warehousing
     ,case when route_contain_main_line = 1 and direct_route_type = 2 and length(in_collect_line_name)>0 then e1_planned_departure else extra_in_planned_departure end extra_in_planned_departure
     ,case when route_contain_main_line = 1 and direct_route_type = 2 and length(in_collect_line_name)>0 then e1_planned_arrival else extra_in_planned_arrival end extra_in_planned_arrival
     ,extra_out_collect_code
     ,extra_out_collect_name
     ,extra_out_line_name
     ,extra_out_latest_warehousing
     ,extra_out_planned_departure
     ,extra_out_planned_arrival
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e1_line_code else in_network_line_name end as in_network_line_name
     ,case when route_contain_main_line = 1 and direct_route_type = 2 and (in_collect_line_name is null or in_collect_line_name='') then e1_line_code else in_collect_line_name end as in_collect_line_name
     ,case when route_contain_main_line = 1 and direct_route_type = 4 then coalesce(e6_line_code,e5_line_code,e4_line_code,e3_line_code,e2_line_code,e1_line_code) else out_network_line_name end as out_network_line_name
     ,case when route_contain_main_line = 1 and direct_route_type = 3 then coalesce(e6_line_code,e5_line_code,e4_line_code,e3_line_code,e2_line_code,e1_line_code) else out_collect_line_name end as out_collect_line_name
     ,town_plus_time
     ,branch_in_ship_time
     ,branch_in_ship_span_days
     ,branch_out_ship_time
     ,branch_out_ship_span_days
     ,all_line_name
     ,out_town_plus_time
     ,in_edge_run_time
     ,in_edge_span_days
     ,in_edge2_run_time
     ,in_edge2_span_days
     ,in_edge3_run_time
     ,in_edge3_span_days
     ,out_edge_run_time
     ,out_edge_span_days
     ,out_edge2_run_time
     ,out_edge2_span_days
     ,out_edge3_run_time
     ,out_edge3_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e2_span_days
           when route_contain_main_line = 1 and direct_route_type = 2 then e2_span_days
           when route_contain_main_line = 1 and direct_route_type = 3 and e2_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e2_line_code is null then null
           else e1_span_days end as e1_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e3_span_days
           when route_contain_main_line = 1 and direct_route_type = 2 then e3_span_days
           when route_contain_main_line = 1 and direct_route_type = 3 and e3_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e3_line_code is null then null
           else e2_span_days end as e2_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e4_span_days
           when route_contain_main_line = 1 and direct_route_type = 2 then e4_span_days
           when route_contain_main_line = 1 and direct_route_type = 3 and e4_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e4_line_code is null then null
           else e3_span_days end as e3_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e5_span_days
           when route_contain_main_line = 1 and direct_route_type = 2 then e5_span_days
           when route_contain_main_line = 1 and direct_route_type = 3 and e5_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e5_line_code is null then null
           else e4_span_days end as e4_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 then e6_span_days
           when route_contain_main_line = 1 and direct_route_type = 2 then e6_span_days
           when route_contain_main_line = 1 and direct_route_type = 3 and e6_line_code is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e6_line_code is null then null
           else e5_span_days end as e5_span_days
     ,case when route_contain_main_line = 1 and direct_route_type = 1 and e5_line_code is not null then out_edge_span_days
           when route_contain_main_line = 1 and direct_route_type = 2 and e5_line_code is not null then out_edge_span_days
           when route_contain_main_line = 1 and direct_route_type = 3 and e5_line_code is not null and out_collect_line_name is null then null
           when route_contain_main_line = 1 and direct_route_type = 4 and e5_line_code is not null and out_collect_line_name is null then null
           else e6_span_days end as e6_span_days
     ,delivery_time
     ,route_flow
     ,in_edge2_latest_warehousing
     ,e1_latest_warehousing
     ,e2_latest_warehousing
     ,e3_latest_warehousing
     ,e4_latest_arrival_time
     ,e5_latest_arrival_time
     ,e6_latest_arrival_time
     ,start_taking_shift
     ,end_send_shift
     ,warehouse_end_time
     ,case when route_contain_main_line = 1 then null else reserve_1 end as reserve_1
     ,case when route_contain_main_line = 1 then null else reserve_2 end as reserve_2
     ,route_contain_main_line
     ,direct_route_type
     ,in_line_day
     ,out_line_day
     ,in_ship_span_days
     ,in_manage_region_code
     ,in_manage_region_name
     ,out_manage_region_code
     ,out_manage_region_name
     ,latest_warehousing_time_quantum
     ,branch_in_total_all_time
     ,main_total_all_time
     ,branch_out_total_all_time
     ,in_last_shift_code
     ,out_last_shift_code
     ,dt
from (
     select *
     from jms_dm.dm_route_whole_base_dt
     where dt = '{{ execution_date | cst_ds }}'
       and route_contain_main_line = 1
       and in_branch_id is not null
) route
distribute by pmod(hash(rand()), 40);


